using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    //    ArrayList table = new ArrayList();
    //    table.Add("-Wybierz-");
    //    DropDownList1.DataSource = table;
    //    DropDownList1.DataBind();
        //DropDownList1.Visible = false;

    }
    //**************************************************
    //   Wyszukiwanie lig w/d podanej nazwy i dat
    //
    //**************************************************
    protected void Button1_Click(object sender, EventArgs e)
    {
        String query = "Select u.Nazwa from Ligi u";
        query += " where u.Nazwa LIKE '" + TextBox1.Text + "%'";
        SqlConnection con = new SqlConnection(SqlDataSource1.ConnectionString);
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        ArrayList table = new ArrayList();
        table.Add("-Wybierz-");
        //ArrayList results = new ArrayList();
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            table.Add(ds.Tables[0].Rows[i]["Nazwa"].ToString());
        }
        DropDownList1.DataSource = table;
        DropDownList1.DataBind();
        DropDownList1.Visible = true;
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        if (DropDownList1.SelectedIndex != 0)
        {
            TextBox1.Text = DropDownList1.Text;
            String query = "Select u.Nazwa from Druzyny u where u.id_druzyny in (select f.id_druzyny from ligi_druzyny f where f.id_ligi in(select m.id_ligi from ligi m where m.nazwa like '" + DropDownList1.Text + "%')  )";
            SqlConnection con = new SqlConnection(SqlDataSource1.ConnectionString);
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            ArrayList table = new ArrayList();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                table.Add(ds.Tables[0].Rows[i]["Nazwa"].ToString());

            }
            //CheckBoxList1.DataSource = table;
            BulletedList2.DataSource = table;
            //for (int i = 0; i < BulletedList2.Items.Count; i++)
            //{
            //    CheckBoxList1.Items[i].Selected = true;
            //}
            BulletedList2.DataBind();
            BulletedList2.Visible = true;
            // uzupelnianie 2 okienek z datami.
            query = "Select MIN(u.data) as cosiek from MECZE u where u.id_ligi = (select f.id_ligi from ligi f where f.nazwa like '" + DropDownList1.Text + "%' )";
            cmd = new SqlCommand(query, con);
            sda = new SqlDataAdapter(cmd);
            ds = new DataSet();
            sda.Fill(ds);
            TextBox2.Text = ds.Tables[0].Rows[0]["cosiek"].ToString();

            query = "Select MAX(u.data) as cosiek from MECZE u where u.id_ligi = (select f.id_ligi from ligi f where f.nazwa like '" + DropDownList1.Text + "%' )";
            cmd = new SqlCommand(query, con);
            sda = new SqlDataAdapter(cmd);
            ds = new DataSet();
            sda.Fill(ds);
            TextBox3.Text = ds.Tables[0].Rows[0]["cosiek"].ToString();


            query = "select id_ligi from ligi where nazwa like '" + DropDownList1.Text + "%'";
            cmd = new SqlCommand(query, con);
            sda = new SqlDataAdapter(cmd);
            ds = new DataSet();
            sda.Fill(ds);
            TextBox4.Text = ds.Tables[0].Rows[0]["id_ligi"].ToString();
            Wizard1.ActiveStepIndex++;
        }

    }
    protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
    {
        

    }


    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void Button4_Click(object sender, EventArgs e)
    {
        Wizard1.ActiveStepIndex++;
        String query = "Select max(kolejka) as cos from mecze where id_ligi = (select id_ligi from ligi where nazwa LIKE '" + DropDownList1.Text + "%' )  ";//ilosc kolejek Select COUNT(Supplier_ID) from suppliers
        SqlConnection con = new SqlConnection(SqlDataSource1.ConnectionString);
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        TextBoxx.Text = ds.Tables[0].Rows[0]["cos"].ToString();


        query = "Select count(*) as cos from mecze where kolejka = 1 and id_ligi = (select id_ligi from ligi where nazwa LIKE '" + DropDownList1.Text + "%' )  ";//ilosc kolejek Select COUNT(Supplier_ID) from suppliers
        con = new SqlConnection(SqlDataSource1.ConnectionString);
        cmd = new SqlCommand(query, con);
        sda = new SqlDataAdapter(cmd);
        ds = new DataSet();
        sda.Fill(ds);
       
        TextBoxy.Text = ds.Tables[0].Rows[0]["cos"].ToString();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {

            SqlConnection sqlConn = new SqlConnection(SqlDataSource1.ConnectionString);
            sqlConn.Open();
            SqlTransaction trans = sqlConn.BeginTransaction();
            SqlCommand sqlCmd = new SqlCommand("UPDATE Ligi SET zakonczona = 't' WHERE id_ligi = '" + TextBox4.Text.ToString() + "'", sqlConn, trans);
            sqlCmd.ExecuteNonQuery();
            trans.Commit();
            Wizard1.ActiveStepIndex = 0;
        
    }
    protected void Button5_Click(object sender, EventArgs e)
    {
        Calendar1.SelectedDate = DateTime.Today;
        Wizard1.ActiveStepIndex++;
        int mecze = Convert.ToInt32(TextBoxy.Text);
        ArrayList table = new ArrayList();
        for (int i = 0; i < mecze; )
        {
            i++;
            table.Add(i.ToString());
        }
        DropDownList2.DataSource = table;
        DropDownList2.DataBind();
        DropDownList2.Visible = true;
        int ii = Convert.ToInt32(TextBoxx.Text);
        table = new ArrayList();
        for (int i = 0; i < ii; )
        {
            i++;
            table.Add(i.ToString());
        }
        DropDownList4.DataSource = table;
        DropDownList4.DataBind();
        DropDownList4.Visible = true;
        ///////////  do edycji.


        /*
        String query = "";
        //
        SqlConnection con = new SqlConnection(SqlDataSource1.ConnectionString);
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);*/
    }
    protected void Button6_Click(object sender, EventArgs e)
    {
        String query = "select id_meczu,dr1.nazwa as nazwa1,dr2.nazwa as nazwa2 from mecze left join druzyny dr1 on mecze.d1 = dr1.id_druzyny left join druzyny dr2 on mecze.d2 = dr2.id_druzyny where kolejka = '" + DropDownList4.Text + "' and id_ligi = '" + TextBox4.Text.ToString()+"'";
        SqlConnection con = new SqlConnection(SqlDataSource1.ConnectionString);
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        ArrayList table = new ArrayList();
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            string temp = ds.Tables[0].Rows[i]["nazwa1"].ToString() + "  vs  "+ ds.Tables[0].Rows[i]["nazwa2"].ToString();
            table.Add(temp);

        }

        BulletedList1.DataSource = table;
        BulletedList1.DataBind();
        BulletedList1.Visible = true;
        //select id_meczu,dr1.nazwa,dr2.nazwa from mecze left join druzyny dr1 where mecze.d1 = dr1.id_druzyny left join druzyny dr2 where mecze.d2 = dr2.id_druzyny 
    }
    protected void Button7_Click(object sender, EventArgs e)
    {
        // dummy
    }
    protected void Button7_Click1(object sender, EventArgs e)
    {
        // update
        if (BulletedList1.Items.Count != 0 )
        {
            int c = Convert.ToInt32(DropDownList2.Text.ToString()) - 1;
            string druzyna1 = BulletedList1.Items[ c ].ToString();
            int temp = druzyna1.IndexOf(" ");
            string druzyna11 = druzyna1.Substring(0, temp - 1);

            String query = "select data from MECZE WHERE druzyna1 LIKE '" + druzyna11 + "%' and kolejka LIKE '" + DropDownList4.Text.ToString() + "' and id_ligi LIKE '" + TextBox4.Text.ToString() + "'";
            SqlConnection con = new SqlConnection(SqlDataSource1.ConnectionString);
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            ArrayList table = new ArrayList();
            
            table.Add(ds.Tables[0].Rows.Count.ToString() + " mecz zmieniono");


            BulletedList1.DataSource = table;
            BulletedList1.DataBind();
            BulletedList1.Visible = true;


            SqlConnection sqlConn = new SqlConnection(SqlDataSource1.ConnectionString);
            sqlConn.Open();
            SqlTransaction trans = sqlConn.BeginTransaction();
            SqlCommand sqlCmd = new SqlCommand("UPDATE Mecze SET data = '" + Calendar1.SelectedDate.ToShortDateString() + "' WHERE druzyna1 LIKE '" + druzyna11 + "%' and kolejka = '" + DropDownList4.Text.ToString() + "' and id_ligi = '" + TextBox4.Text.ToString() + "'", sqlConn, trans);
            sqlCmd.ExecuteNonQuery();
            trans.Commit();
        }
    }
}
